// Project: Salient
// http://salient.codeplex.com
// 
// Copyright 2010, Sky Sanders <sky at skysanders.net>
// Dual licensed under the MIT or GPL Version 2 licenses.
// http://salient.codeplex.com/license
//  
// Date: May 24 2010 

#region

using System;
using System.Collections.Specialized;
using System.Data.SqlClient;
using System.Reflection;
using System.Text;

#endregion

namespace Salient.SqlServer.Management
{
    ///<summary>
    ///</summary>
    public class Database : SqlCmdRunner
    {
        #region Constants

        private const string DropDb =
                        @"USE [master]
                        GO

                        IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = '{0}') 
                        begin
	                        ALTER DATABASE {0} SET SINGLE_USER WITH ROLLBACK IMMEDIATE
	                        DROP DATABASE {0}
	                        PRINT('Database [{0}] dropped for recreation...')
                        end

                        GO";

        private const string UseDb = "USE {0}\r\nGO";

        #endregion

        #region Fields

        ///<summary>
        ///</summary>
        public bool AllowSnapshotIsolation;

        ///<summary>
        ///</summary>
        public bool AnsiNullDefault;

        ///<summary>
        ///</summary>
        public bool AnsiNulls;

        ///<summary>
        ///</summary>
        public bool AnsiPadding;

        ///<summary>
        ///</summary>
        public bool AnsiWarnings;

        ///<summary>
        ///</summary>
        public bool Arithabort;

        ///<summary>
        ///</summary>
        public bool AutoClose;

        ///<summary>
        ///</summary>
        public bool AutoCreateStatistics;

        ///<summary>
        ///</summary>
        public bool AutoShrink;

        ///<summary>
        ///</summary>
        public bool AutoUpdateStatistics;

        ///<summary>
        ///</summary>
        public bool AutoUpdateStatisticsAsync;

        ///<summary>
        ///</summary>
        public bool ConcatNullYieldsNull;

        ///<summary>
        ///</summary>
        public bool CursorCloseOnCommit;

        ///<summary>
        ///</summary>
        public bool DateCorrelationOptimization;

        ///<summary>
        ///</summary>
        public bool DbChaining;

        ///<summary>
        ///</summary>
        public bool FullText;

        ///<summary>
        ///</summary>
        public bool NumericRoundabort;

        ///<summary>
        ///</summary>
        public bool QuotedIdentifier;

        ///<summary>
        ///</summary>
        public bool ReadCommittedSnapshot;

        ///<summary>
        ///</summary>
        public bool RecursiveTriggers;

        ///<summary>
        ///</summary>
        public bool Trustworthy;

        #endregion

        #region Constructors

        ///<summary>
        ///</summary>
        ///<param name="dataSource"></param>
        ///<param name="initialCatalog"></param>
        ///<param name="userId"></param>
        ///<param name="password"></param>
        public Database(string dataSource, string initialCatalog, string userId, string password)
        {
            DataSource = dataSource;
            InitialCatalog = initialCatalog;
            UserID = userId;
            Password = password;
        }

        ///<summary>
        ///</summary>
        ///<param name="connectionString"></param>
        public Database(string connectionString)
        {
            ConnectionString = connectionString;
        }

        ///<summary>
        ///</summary>
        ///<param name="dataSource"></param>
        ///<param name="initialCatalog"></param>
        public Database(string dataSource, string initialCatalog)
        {
            DataSource = dataSource;
            InitialCatalog = initialCatalog;
            IntegratedSecurity = true;
        }

        #endregion

        #region Public Methods

        ///<summary>
        ///</summary>
        public void Create()
        {
            Create(new StringCollection());
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="scripts">Batches to run after database creation. Do NOT include CREATE DATABASE batch. It is included by this method.</param>
        public void Create(StringCollection scripts)
        {
            // in reverse order - feel me?
            scripts.Insert(0, string.Format(UseDb, InitialCatalog));
            scripts.Insert(0, GetCreateScript(InitialCatalog));

            SqlCmdRunner runner = new SqlCmdRunner
                {
                    ConnectionString =
                        new SqlConnectionStringBuilder(ConnectionString) { InitialCatalog = "Master" }.
                        ConnectionString
                };
            runner.ExecuteScript(scripts);
        }

        ///<summary>
        ///</summary>
        public void Drop()
        {
            StringCollection query = new StringCollection
                {
                    string.Format(DropDb,
                                  InitialCatalog)
                };

            SqlCmdRunner runner = new SqlCmdRunner
                {
                    ConnectionString =
                        new SqlConnectionStringBuilder(ConnectionString) { InitialCatalog = "Master" }.
                        ConnectionString
                };

            runner.ExecuteScript(query);
        }

        ///<summary>
        ///</summary>
        ///<param name="scripts"></param>
        public void DropAndCreate(StringCollection scripts)
        {
            Drop();
            Create(scripts);

        }

        ///<summary>
        ///</summary>
        public void DropAndCreate(string script)
        {
            DropAndCreate(new StringCollection { script });
        }
        ///<summary>
        ///</summary>
        public void DropAndCreate()
        {
            DropAndCreate(new StringCollection());
        }

        /// <summary>
        /// Fix for 'Instance names used for writing to custom counters must be 127 characters or less'
        /// </summary>
        /// <param name="name"></param>
        public static void SetupFriendlyName(string name)
        {
            typeof(AppDomain).GetMethod("nSetupFriendlyName", BindingFlags.NonPublic | BindingFlags.Instance).Invoke(AppDomain.CurrentDomain, new object[] { name });
        }

        #endregion

        #region Private Methods

        private static string BoolToOnOff(bool value)
        {
            return value ? "ON" : "OFF";
        }

        private string GetCreateScript(string name)
        {
            StringBuilder sb = new StringBuilder();

            sb.AppendFormat("  USE [master]\r\n");
            sb.AppendFormat("  GO\r\n");
            sb.AppendFormat("  CREATE DATABASE [{0}]\r\n", name);
            if (FullText)
            {
                sb.AppendFormat("  IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))\r\n");
                sb.AppendFormat("  begin\r\n");
                sb.AppendFormat("  	EXEC [{0}].[dbo].[sp_fulltext_database] @action = 'enable'\r\n", name);
                sb.AppendFormat("  end\r\n");
            }
            sb.AppendFormat("  ALTER DATABASE [{0}] SET ANSI_NULL_DEFAULT {1}                \r\n", name,
                            BoolToOnOff(AnsiNullDefault));
            sb.AppendFormat("  ALTER DATABASE [{0}] SET ANSI_NULLS {1}                       \r\n", name,
                            BoolToOnOff(AnsiNulls));
            sb.AppendFormat("  ALTER DATABASE [{0}] SET ANSI_PADDING {1}                     \r\n", name,
                            BoolToOnOff(AnsiPadding));
            sb.AppendFormat("  ALTER DATABASE [{0}] SET ANSI_WARNINGS {1}                    \r\n", name,
                            BoolToOnOff(AnsiWarnings));
            sb.AppendFormat("  ALTER DATABASE [{0}] SET ARITHABORT {1}                       \r\n", name,
                            BoolToOnOff(Arithabort));
            sb.AppendFormat("  ALTER DATABASE [{0}] SET AUTO_CLOSE {1}                       \r\n", name,
                            BoolToOnOff(AutoClose));
            sb.AppendFormat("  ALTER DATABASE [{0}] SET AUTO_CREATE_STATISTICS {1}           \r\n", name,
                            BoolToOnOff(AutoCreateStatistics));
            sb.AppendFormat("  ALTER DATABASE [{0}] SET AUTO_SHRINK {1}                      \r\n", name,
                            BoolToOnOff(AutoShrink));
            sb.AppendFormat("  ALTER DATABASE [{0}] SET AUTO_UPDATE_STATISTICS {1}           \r\n", name,
                            BoolToOnOff(AutoUpdateStatistics));
            sb.AppendFormat("  ALTER DATABASE [{0}] SET CURSOR_CLOSE_ON_COMMIT {1}           \r\n", name,
                            BoolToOnOff(CursorCloseOnCommit));
            sb.AppendFormat("  ALTER DATABASE [{0}] SET CONCAT_NULL_YIELDS_NULL {1}          \r\n", name,
                            BoolToOnOff(ConcatNullYieldsNull));
            sb.AppendFormat("  ALTER DATABASE [{0}] SET NUMERIC_ROUNDABORT {1}               \r\n", name,
                            BoolToOnOff(NumericRoundabort));
            sb.AppendFormat("  ALTER DATABASE [{0}] SET QUOTED_IDENTIFIER {1}                \r\n", name,
                            BoolToOnOff(QuotedIdentifier));
            sb.AppendFormat("  ALTER DATABASE [{0}] SET RECURSIVE_TRIGGERS {1}               \r\n", name,
                            BoolToOnOff(RecursiveTriggers));
            sb.AppendFormat("  ALTER DATABASE [{0}] SET AUTO_UPDATE_STATISTICS_ASYNC {1}     \r\n", name,
                            BoolToOnOff(AutoUpdateStatisticsAsync));
            sb.AppendFormat("  ALTER DATABASE [{0}] SET DATE_CORRELATION_OPTIMIZATION {1}    \r\n", name,
                            BoolToOnOff(DateCorrelationOptimization));
            sb.AppendFormat("  ALTER DATABASE [{0}] SET TRUSTWORTHY {1}                      \r\n", name,
                            BoolToOnOff(Trustworthy));
            sb.AppendFormat("  ALTER DATABASE [{0}] SET ALLOW_SNAPSHOT_ISOLATION {1}         \r\n", name,
                            BoolToOnOff(AllowSnapshotIsolation));
            sb.AppendFormat("  ALTER DATABASE [{0}] SET READ_COMMITTED_SNAPSHOT {1}          \r\n", name,
                            BoolToOnOff(ReadCommittedSnapshot));
            sb.AppendFormat("  ALTER DATABASE [{0}] SET DB_CHAINING {1}                      \r\n", name,
                            BoolToOnOff(DbChaining));
            sb.AppendFormat("  ALTER DATABASE [{0}] SET PARAMETERIZATION SIMPLE \r\n", name);
            sb.AppendFormat("  ALTER DATABASE [{0}] SET CURSOR_DEFAULT  GLOBAL  \r\n", name);
            sb.AppendFormat("  ALTER DATABASE [{0}] SET DISABLE_BROKER          \r\n", name);
            sb.AppendFormat("  ALTER DATABASE [{0}] SET READ_WRITE              \r\n", name);
            sb.AppendFormat("  ALTER DATABASE [{0}] SET RECOVERY SIMPLE         \r\n", name);
            sb.AppendFormat("  ALTER DATABASE [{0}] SET MULTI_USER              \r\n", name);
            sb.AppendFormat("  ALTER DATABASE [{0}] SET PAGE_VERIFY CHECKSUM    \r\n", name);
            sb.AppendFormat("  PRINT('Database [{0}] created...')               \r\n", name);
            sb.AppendFormat("  GO\r\n");
            return sb.ToString();
        }

        #endregion

    }
}